USE db_YTeTB

------------ Table ChuyenKho ---------------------------
CREATE TABLE [dbo].[ChuyenKho](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OldKhoID] [int] NOT NULL,
	[NewKhoID] [int] NOT NULL,
	[Time] [date] NOT NULL,
	[CodeChuyen] [nvarchar](50) NOT NULL,
	[SanPhamID] [ntext] NOT NULL,
	[SoLuong] [ntext] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_ChuyenKho] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ChuyenKho]  WITH CHECK ADD  CONSTRAINT [FK_ChuyenKho_Kho] FOREIGN KEY([NewKhoID])
REFERENCES [dbo].[Kho] ([Id])
GO

ALTER TABLE [dbo].[ChuyenKho] CHECK CONSTRAINT [FK_ChuyenKho_Kho]
GO

ALTER TABLE [dbo].[ChuyenKho]  WITH CHECK ADD  CONSTRAINT [FK_ChuyenKho_Kho1] FOREIGN KEY([OldKhoID])
REFERENCES [dbo].[Kho] ([Id])
GO

ALTER TABLE [dbo].[ChuyenKho] CHECK CONSTRAINT [FK_ChuyenKho_Kho1]
GO

ALTER TABLE [dbo].[ChuyenKho]  WITH CHECK ADD  CONSTRAINT [FK_ChuyenKho_UserProfile] FOREIGN KEY([UserID])
REFERENCES [dbo].[UserProfile] ([UserId])
GO

ALTER TABLE [dbo].[ChuyenKho] CHECK CONSTRAINT [FK_ChuyenKho_UserProfile]
GO
------------ EndTable ChuyenKho ---------------------------

------------ Table CuaHang ---------------------------
CREATE TABLE [dbo].[CuaHang](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TenCuaHang] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_CuaHang] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO dbo.[CuaHang] (TenCuaHang) 
    VALUES (N'Cửa hàng chính');
------------ EndTable CuaHang ---------------------------

------------ Table DVCC ---------------------------
ALTER TABLE [dbo].[DVCC] add CuaHangID int null
GO
update [dbo].[DVCC] set [CuaHangID] = 1
GO
ALTER TABLE [dbo].[DVCC] alter column [CuaHangID] int not null
GO
ALTER TABLE [dbo].[DVCC]  WITH CHECK ADD  CONSTRAINT [FK_DVCC_CuaHang] FOREIGN KEY([CuaHangID])
REFERENCES [dbo].[CuaHang] ([ID])
GO

ALTER TABLE [dbo].[DVCC] CHECK CONSTRAINT [FK_DVCC_CuaHang]
GO
------------ EndTable DVCC ---------------------------

------------ Table KhachHang ---------------------------
ALTER TABLE [dbo].[KhachHang] add CuaHangID int null
GO
update [dbo].[KhachHang] set [CuaHangID] = 1
GO
ALTER TABLE [dbo].[KhachHang] alter column [CuaHangID] int not null
GO
ALTER TABLE [dbo].[KhachHang]  WITH CHECK ADD  CONSTRAINT [FK_KhachHang_CuaHang] FOREIGN KEY([CuaHangID])
REFERENCES [dbo].[CuaHang] ([ID])
GO

ALTER TABLE [dbo].[KhachHang] CHECK CONSTRAINT [FK_KhachHang_CuaHang]
GO
------------ EndTable KhachHang ---------------------------

------------ Table Kho ---------------------------
ALTER TABLE [dbo].[Kho] add CuaHangId int null
GO
update [dbo].[Kho] set [CuaHangId] = 1
GO
ALTER TABLE [dbo].[Kho] alter column CuaHangId int not null
GO
ALTER TABLE [dbo].[Kho]  WITH CHECK ADD  CONSTRAINT [FK_Kho_CuaHang] FOREIGN KEY([CuaHangId])
REFERENCES [dbo].[CuaHang] ([ID])
GO

ALTER TABLE [dbo].[Kho] CHECK CONSTRAINT [FK_Kho_CuaHang]
GO
------------ EndTable Kho ---------------------------

------------ Table NhapKho ---------------------------
ALTER TABLE [dbo].[NhapKho] add KhoID int null
GO
update [dbo].[NhapKho] set [KhoID] = 4
GO
ALTER TABLE [dbo].[NhapKho] alter column KhoID int not null
GO
ALTER TABLE [dbo].[NhapKho]  WITH CHECK ADD  CONSTRAINT [FK_NhapKho_Kho] FOREIGN KEY([KhoID])
REFERENCES [dbo].[Kho] ([Id])
GO

ALTER TABLE [dbo].[NhapKho] CHECK CONSTRAINT [FK_NhapKho_Kho]
GO

Alter table [nhapkho] drop FK_NhapKho_NhanVien
GO
update [NhapKho] set ID_NguoiNhapKho = 1
GO
ALTER TABLE [dbo].[NhapKho]  WITH CHECK ADD  CONSTRAINT FK_NhapKho_UserProfile FOREIGN KEY(ID_NguoiNhapKho)
REFERENCES [dbo].[UserProfile] ([UserId])
GO

ALTER TABLE [dbo].[NhapKho] CHECK CONSTRAINT FK_NhapKho_UserProfile
GO
------------ EndTable NhapKho ---------------------------

------------ Table SanPhamInKho ---------------------------
CREATE TABLE [dbo].[SanPhamInKho](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SanPhamID] [int] NOT NULL,
	[KhoID] [int] NOT NULL,
	[SoLuong] [int] NOT NULL,
 CONSTRAINT [PK_SanPhamInKho] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SanPhamInKho]  WITH CHECK ADD  CONSTRAINT [FK_SanPhamInKho_Kho] FOREIGN KEY([KhoID])
REFERENCES [dbo].[Kho] ([Id])
GO

ALTER TABLE [dbo].[SanPhamInKho] CHECK CONSTRAINT [FK_SanPhamInKho_Kho]
GO

ALTER TABLE [dbo].[SanPhamInKho]  WITH CHECK ADD  CONSTRAINT [FK_SanPhamInKho_SanPham] FOREIGN KEY([SanPhamID])
REFERENCES [dbo].[SanPham] ([ID])
GO

ALTER TABLE [dbo].[SanPhamInKho] CHECK CONSTRAINT [FK_SanPhamInKho_SanPham]
GO

insert into SanPhamInKho(KhoID, SoLuong, SanPhamID)
select ID_KhoMD, SoLuong, ID from SanPham where (Disable is null or Disable = '') and SoLuong is not null

------------ EndTable SanPhamInKho ---------------------------

------------ Table SanPham ---------------------------
alter table [dbo].[SanPham] drop FK_SanPham_Kho
GO
ALter table [dbo].[SanPham] drop column ID_KhoMD
GO
------------ EndTable SanPham ---------------------------

------------ Table ThanhToan ---------------------------
--nothing to change
------------ EndTable ThanhToan ---------------------------

------------ Table UserInCuaHang ---------------------------

CREATE TABLE [dbo].[UserInCuaHang](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CuaHangID] [int] NOT NULL,
	[UserId] [int] NOT NULL,
 CONSTRAINT [PK_UserInCuaHang] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserInCuaHang]  WITH CHECK ADD  CONSTRAINT [FK_UserInCuaHang_CuaHang] FOREIGN KEY([CuaHangID])
REFERENCES [dbo].[CuaHang] ([ID])
GO

ALTER TABLE [dbo].[UserInCuaHang] CHECK CONSTRAINT [FK_UserInCuaHang_CuaHang]
GO

ALTER TABLE [dbo].[UserInCuaHang]  WITH CHECK ADD  CONSTRAINT [FK_UserInCuaHang_UserProfile] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserProfile] ([UserId])
GO

ALTER TABLE [dbo].[UserInCuaHang] CHECK CONSTRAINT [FK_UserInCuaHang_UserProfile]
GO

INSERT INTO dbo.[UserInCuaHang] (CuaHangID, UserId) 
    VALUES (1, 1);
INSERT INTO dbo.[UserInCuaHang] (CuaHangID, UserId) 
    VALUES (1, 12);
INSERT INTO dbo.[UserInCuaHang] (CuaHangID, UserId) 
    VALUES (1, 13);

------------ EndTable UserInCuaHang ---------------------------

------------ Table UserProfile ---------------------------
Alter table [dbo].[UserProfile] add DisplayName nvarchar(56) null
GO
Alter table [dbo].[UserProfile] add Code nvarchar(10) null
GO
Alter table [dbo].[UserProfile] add Phone nvarchar(11) null
GO
Alter table [dbo].[UserProfile] add Status bit null
GO
update [dbo].[UserProfile] set DisplayName=N'Nhân viên nhập kho', Code='NVNK', Status=1 where UserId=13
GO
update [dbo].[UserProfile] set DisplayName=N'Nhân viên xuất kho', Code='NVNK', Status=1 where UserId=12
GO
update [dbo].[UserProfile] set DisplayName=N'Administrator', Code='adm', Status=1 where UserId=1
GO
------------ EndTable UserProfile ---------------------------

------------ Table XuatKho ---------------------------
ALTER TABLE [dbo].[XuatKho] add KhoID int null
GO
update [dbo].[XuatKho] set [KhoID] = 4
GO
ALTER TABLE [dbo].[XuatKho] alter column KhoID int not null
GO
ALTER TABLE [dbo].[XuatKho]  WITH CHECK ADD  CONSTRAINT [FK_XuatKho_Kho] FOREIGN KEY([KhoID])
REFERENCES [dbo].[Kho] ([Id])
GO

ALTER TABLE [dbo].[XuatKho] CHECK CONSTRAINT [FK_XuatKho_Kho]
GO

Alter table [XuatKho] drop FK_XuatKho_NhanVien
GO
update [XuatKho] set ID_NguoiXuatKho = 1
GO
ALTER TABLE [dbo].[XuatKho]  WITH CHECK ADD  CONSTRAINT FK_XuatKho_UserProfile FOREIGN KEY(ID_NguoiXuatKho)
REFERENCES [dbo].[UserProfile] ([UserId])
GO

ALTER TABLE [dbo].[XuatKho] CHECK CONSTRAINT FK_XuatKho_UserProfile
GO
------------ EndTable XuatKho ---------------------------

Drop table NhanVien